﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using CodeWork.Library.DataStructure;

namespace CodeWork.Library.Data
{
    public class DatabaseUtility
    {
        public static DataTable GetProviders()
        {
            // ValueMember = "Description"
            // DisplayMember = "Name"
            DataTable providerList = DbProviderFactories.GetFactoryClasses();
            return providerList;
        }

        public static DataTable GetServers(DataRowView providerInfo)
        {
            DbProviderFactory factory = DbProviderFactories.GetFactory(providerInfo.Row);
            // row["ServerName"].ToString() + "\\" + row["InstanceName"].ToString()
            DataTable serverList = factory.CreateDataSourceEnumerator().GetDataSources();
            return serverList;
        }

        public static DataTable GetDatabases(DataRowView serverInfo)
        {
            if (serverInfo.Row["InvariantName"].ToString() == "System.Data.SqlClient")
            {
                string serverName = serverInfo["ServerName"].ToString() + "\\" + serverInfo["InstanceName"].ToString();
                SqlConnection connection = ConnectToSqlServer(serverName);
                connection.Open();

                // Database names = database_name
                DataTable tblDatabases = connection.GetSchema("Databases");
                connection.Close();
                return tblDatabases;
            }
            else
                return new DataTable();
        }

        public static SqlConnection ConnectToSqlServer(string serverName)
        {
            SqlConnection connection = new SqlConnection();
            StringBuilder connectionString = new StringBuilder();
            connectionString.Append("Data Source=").Append(serverName).Append("; Integrated Security=True;");
            connection.ConnectionString = connectionString.ToString();

            return connection;
        }

        public static SqlConnection ConnectToSqlServer(string serverName, string databaseName)
        {
            SqlConnection connection = new SqlConnection();
            StringBuilder connectionString = new StringBuilder();
            connectionString.Append("Data Source=").Append(serverName).Append(";Initial Catalog = ").Append(databaseName).Append("; Integrated Security=True;");
            connection.ConnectionString = connectionString.ToString();

            return connection;
        }

        public static TableColumn ReadTableColumn(SqlDataReader reader)
        {
            string columnName = reader["column_name"] == System.DBNull.Value ? string.Empty : reader["column_name"].ToString();
            string dataType = reader["data_type"] == System.DBNull.Value ? string.Empty : reader["data_type"].ToString();
            int characterMaximumLength = reader["character_maximum_length"] == System.DBNull.Value ? 0 : (int)reader["character_maximum_length"];

            TableColumn tableColumn = new TableColumn(columnName, dataType, characterMaximumLength);
            return tableColumn;
        }

        public static List<Table> GetTables(string serverName, string databaseName)
        {
            SqlCommand command = null;
            SqlDataReader reader = null;
            try
            {
                SqlConnection connection = ConnectToSqlServer(serverName, databaseName);

                string sql = @"SELECT table_name, column_name, data_type, character_maximum_length
                            FROM information_schema.columns
                            WHERE table_name IN
                          (
                            SELECT table_name
                            FROM Information_Schema.Tables
                            WHERE Table_Type='Base Table'
                            AND table_name LIKE 'Forms_W4Forms_State_%'
                          ) ORDER BY table_name;";

                command = connection.CreateCommand();
                command.CommandText = sql;
                command.Connection.Open();
                reader = command.ExecuteReader();

                List<Table> tables = new List<Table>();
                Table lastTable = null;
                while (reader.Read())
                {
                    string tableName = reader["table_name"] == null ? string.Empty : reader["table_name"].ToString();
                    if (lastTable != null)
                    {
                        if (tableName == lastTable.TableName)
                        {
                            TableColumn tableColumn = ReadTableColumn(reader);
                            lastTable.Columns.Add(tableColumn);
                        }
                        else
                        {
                            List<TableColumn> tableColumns = new List<TableColumn>();

                            TableColumn tableColumn = ReadTableColumn(reader);
                            tableColumns.Add(tableColumn);
                            Table table = new Table(tableName, tableColumns);
                            tables.Add(lastTable);
                            lastTable = table;
                        }
                    }
                    else
                    {
                        List<TableColumn> tableColumns = new List<TableColumn>();

                        TableColumn tableColumn = ReadTableColumn(reader);
                        tableColumns.Add(tableColumn);
                        Table table = new Table(tableName, tableColumns);
                        lastTable = table;
                    }
                }
                if (lastTable != null)
                    tables.Add(lastTable);

                return tables;
            }
            catch (SqlException ex)
            {
                return new List<Table>();
            }
            catch (Exception ex)
            {
                return new List<Table>();
            }
            finally
            {
                if (!reader.IsClosed)
                    reader.Close();
                command.Connection.Close();
            }
        }
    }
}
